import urllib.request
import bs4
import re
import xlwt
import sqlite3
def main():
    baseUrl="https://yd.qq.com/web/category/"
    savepath="wxbooks_21.xls"
    dbPath="wxbooks.db"
    #askUrl(baseUrl)
    list=getData(baseUrl)
    saveData(list,savepath)#保存到xls文件


#图书名
findbookTitle=re.compile(r'<p class="wr_bookList_item_title">(.*?)</p>')
#作者
findWriter=re.compile(r'<a href="/web/search/books.author=.*">(.*?)</a>')
#推荐值
findRP=re.compile(r'<span class="wr_bookList_item_reading_percent">(.*?)</span>')
#阅读人数
findReaderNum=re.compile(r'<span class="wr_bookList_item_reading_number">(.*?)</span>')
#图书类别数组
catalog=['社会小说','情感小说','青春文学','科幻经典','战争军旅','玄幻精品','恐怖惊悚','悬疑推理','世界名著','外国文学','武侠经典']
def getData(baseUrl):
    dataList=[]
    for n in range(1,12):#调用获取页面信息的函数11次
        url=baseUrl+str(100000+n)
        html=askUrl(url)#保存获取的网页源码
        #解析网页内容
        soup=bs4.BeautifulSoup(html,"html.parser")

        for item in soup.find_all('div',class_="wr_bookList_item_container"):#查找符合要求的字符串生成列表

            data=[]
            type=catalog[n-1]
            i=str(item)


            try:
                title=re.findall(findbookTitle,i)[0]
                data.append(title)
                writer=re.findall(findWriter,i)[0]
                data.append(writer)
                rp=re.findall(findRP,i)[0]
                data.append(rp)
                readerNum=re.findall(findReaderNum,i)[0]
                data.append(readerNum)
                data.append(type)
                dataList.append(data)



            except IndexError as e:
                print("越界!!!!!!!!")

    #print(dataList)
    return dataList

def askUrl(url):
    head={
        'User-Agent': 'Mozilla / 5.0(Windows NT 10.0;Win64;x64;rv: 99.0) Gecko / 20100101 Firefox / 99.0'
        #User-Agent一定要写对
    }
    req=urllib.request.Request(url=url,headers=head)
    html=""
    try:
        response=urllib.request.urlopen(req)
        html=response.read().decode("utf-8")
        #print(html)
    except urllib.error.URLError as e:
        if hasattr(e,"code"):
            print(e.code)
        if hasattr(e,"reason"):
            print(e.reason)
    return html

def saveData(dataList,savePath):
    print("save...")
    book=xlwt.Workbook(encoding="utf-8",style_compression=0)
    sheet=book.add_sheet("wxbooks")
    col=("书名","作者","推荐值","阅读人数","图书类别")
    for i in range(0,5):
        sheet.write(0,i,col[i])
    for j in range(0,len(dataList)):
        data=dataList[j]
        for m in range(0,5):
            sheet.write(j+1,m,data[m])
    book.save(savePath)

def initDB(dbPath):
    conn = sqlite3.connect(dbPath)
    c = conn.cursor()
    sql='''create table top250Movies
            (id integer primary key autoincrement,
            link text,
            image text,
            ch_name varchar(50),
            en_name varchar(50),
            rating double,
            detail text)'''
    c.execute(sql)
    conn.close()
    print("完成")


def saveData_db(dataList,dbPath):
    conn = sqlite3.connect(dbPath)
    c = conn.cursor()
    for data in dataList:
        link='"'+data[0]+'"'
        image='"'+data[1]+'"'
        ch_name='"'+data[2]+'"'
        en_name='"'+data[3]+'"'
        rating=data[4]
        detail='"'+data[5]+'"'#注意单引号的使用，数据中可能会含有单引号
        sql=f"insert into top250Movies(link,image,ch_name,en_name,rating,detail) values({link},{image},{ch_name},{en_name},{rating},{detail})"
        print(sql)
        c.execute(sql)
        print("插入一条记录")
    c.close()
    conn.commit()
    conn.close()
    print("完成")

if __name__ == '__main__':
    main()
    #initDB("doubanMovie.db")
